	Step 8.3 DDL to create the object-relational database, using Oracle syntax.
CREATE OR REPLACE TYPE AddressType AS OBJECT(
street		VARCHAR2(50)
city		VARCHAR2(15)
state		CHAR(2)
zip		CHAR(10));

CREATE OR REPLACE PhoneType AS OBJECT(
areaCode	CHAR(3),
telephoneNumber	CHAR(7));

CREATE OR REPLACE NameType AS OBJECT(
firstName	VARCHAR2 (15), 
lastName	VARCHAR2 (20));

CREATE OR REPLACE OwnerType AS OBJECT (
ownerId		NUMBER (6),
ownerType	VARCHAR2 (9));

CREATE OR REPLACE TABLE Owners OF OwnerType
REF IS ownid SYSTEM GENERATED
CONSTRAINT Owners_ownerId_pk PRIMARY KEY ownerId);

CREATE OR REPLACE TYPE ArtistType AS OBJECT (
artistId 		NUMBER(6), 	
name			NameType,
interviewDate	 	DATE, 
interviewerName 	NameType, 
phone			PhoneType,
address			AddressType,
salesLastYear 		NUMBER(8,2), 
salesYearToDate	NUMBER(8,2), 
socialSecurityNumber 	CHAR(9), 	
usualMedium		VARCHAR	2(15), 
usualStyle 		VARCHAR	2(15), 
usualType 		VARCHAR	2(20),
ownid			REF(OwnerType) SCOPE Owners);
CREATE OR REPLACE TABLE Artists OF ArtistType(
REF IS aid SYSTEM GENERATED
CONSTRAINT Artists_artistId_pk PRIMARY KEY ArtistId,
CONSTRAINT Artists_socialSecurityNumber_uk UNIQUE socialSecurityNumber,
CONSTAINT Artists_name-uk UNIQUE name);

CREATE OR REPLACE CollectorType AS OBJECT (
socialSecurityNumber 	CHAR(9), 	
name			NameType, 
interviewDate 		DATE, 
interviewerName	 	NameType,
phone			Phonetype,
address			Addresstype,
salesLastYear 		NUMBER	(8,2),
salesYearToDate		NUMBER(8,2)
ownid			REF(OwnerType) SCOPE Owners);

CREATE OR REPLACE TABLE Collectors OF CollectorType( 
REF is cid SYSTEM GENERATED.
CONSTRAINT Collectors_SSN_pk PRIMARY KEY socialSecurityNumber);

CREATE OR REPLACE TYPE CustomerType AS OBJECT (
customerId		NUMBER(6),
Name			NameType,
Phone			phoneType,
Address			AddressType,
dateFilledIn 		DATE 
aid			REF(ArtistType) SCOPE Artists,
preferredMedium 	VARCHAR2(15), 
preferredStyle 	VARCHAR2(15), 
preferredType 		VARCHAR2(20));

CREATE OR REPLACE TABLE Customers OF CustomerType(
REF IS cid SYSTEM GENERATED,
CONSTRAINT Customers_customerId_pk PRIMARY KEY (customerId));

CREATE OR REPLACE TYPE BuyerType UNDER CustomerType AS OBJECT (
purchasesLastYear 		NUMBER	(8,2) 
purchasesYearToDate 	NUMBER(8,2));

CREATE OR REPLACE TABLE Buyers OF BuyerType UNDER Customers;

CREATE OR REPLACE TYPE ArtworkType AS OBJECT (
artworkId 	NUMBER(6),
aId 		REF(ArtistType) SCOPE(Artists),
workTitle 	VARCHAR2(50), 
askingPrice 	NUMBER(8,2), 
dateListed 	DATE, 
dateReturned	 DATE, 
dateShown	 DATE, 
status 		VARCHAR2(15), 
workMedium 	VARCHAR2(15), 
workSize		 VARCHAR2(15), 
workStyle		 VARCHAR2(15), 
workType 	VARCHAR2(20), 
workYearCompleted CHAR(4), 
ownId		REF (OwnerType) SCOPE (Owners);

CREATE OR REPLACE TABLE Artworks OF ArtworkType(
REF IS workid SYSTEM GENERATED
CONSTRAINT Artwork_artworkId_pk PRIMARY KEY (artworkId),
CONSTRAINT Artwork_artistId_workTitle_uk UNIQUE (artistId, workTitle));

CREATE OR REPLACE TYPE ReturedWorkType UNDER ArtworkType AS OBJECT (
dateReturned 	DATE;

CREATE OR REPLACE TABLE ReturnedWorks UNDER Artworks(
returnedWork		ReturnedWorkType);

CREATE OR REPLACE TYPE WorkForSaleType UNDER ArtworkType AS OBJECT (
Location	VARCHAR2(50);

CREATE OR REPLACE TABLE WorksForSale UNDER Artworks(
workForSale	WorkForSaleType);

CREATE OR REPLACE TYPE SoldWorkType UNDER ArtworkType AS OBJECT (
dateSold	DATE
cid		REF(CustomerType) SCOPE Buyers;
;

CREATE OR REPLACE TABLE SoldWorks UNDER Artworks(
soldWork		SoldWorkType);
 

CREATE OR REPLACE TYPE ShowType AS OBJECT (
ShowTitle 		VARCHAR2(50),
aid			 REF(artistType) SCOPE Artists,	
showClosingDate		 DATE, 
showTheme		 VARCHAR2(50), 
showOpeningDate 		 DATE);

CREATE OR REPLACE TABLE Shows of ShowType (
REF is showId SYSTEM GENERATED,
CONSTRAINT Shows_ShowTitle_pk PRIMARY KEY ShowTitle);

CREATE TABLE ShownIn
(workId		REF(ArtworkType) SCOPE Artworks
showId		REF(ShowType) SCOPE Shows,
PRIMARY KEY(workId, showid); 

CREATE OR REPLACE TYPE SalespersonType AS OBJECT ( 
socialSecurityNumber	 CHAR(9) 	
name			NameType
address			AddressType;

CREATE OR REPLACE TABLE Salespeople OF SalespersonType
REF is sid SYSTEM GENERATED,
CONSTRAINT Salespeople_SSN_pk PRIMARY KEY socialSecurityNumber;

CREATE OR REPLACE TYPE SaleType AS OBJECT (
invoiceNumber 		NUMBER(6), 	
workId			REF(ArtworkType) SCOPE Artworks, 		
amountRemittedToOwner 	NUMBER	(8,2) 	DEFAULT 0.00, 
saleDate			DATE,
salePrice 			NUMBER	(8,2), 
saleTax 		NUMBER	(6,2), 
sid			REF(SalespersonType) SCOPE Salespeople);

CREATE OR REPLACE TABLE Sales OF SaleType
CONSTRAINT Sales_invoiceNumber_pk PRIMARY KEY invoiceNumber;
CREATE OR REPLACE TYPE CollectionType AS OBJECT (
cid			REF (CollectorType) SCOPE Collectors NOT NULL,
aid			REF(ArtistType) SCOPE Artists,
collectionMedium 		VARCHAR2(15), 
collectionStyle 		VARCHAR2(15), 
collectionType 		VARCHAR2(20)); 

CREATE OR REPLACE TABLE Collections OF CollectionType(
CONSTRAINT Collections_cid_aid_pk PRIMARY KEY (cid,aid);
	
